﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using KVB.Common;

namespace Customer_Profile.eCard
{
    public partial class ImportDeals : System.Web.UI.Page
    {
        string filename = "";
        DataSet ds = new DataSet();
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            filename = fileupdown();
            hidFileName.Value = filename;
            fillGrid(filename,"Data");
        }
        public string fileupdown()
        {
            if (Path.GetExtension(FileUpload1.FileName) == ".txt" || Path.GetExtension(FileUpload1.FileName) == ".xlsx" || Path.GetExtension(FileUpload1.FileName) == ".tsv")
            {
                string filename = FileUpload1.FileName; //FileUpload1.PostedFile.FileName.Substring(FileUpload1.PostedFile.FileName.LastIndexOf("\\"));
                FileUpload1.PostedFile.SaveAs(Server.MapPath("fileupdown\\") + filename);//上传文件
                FileUpload1.PostedFile.InputStream.Close();
                lblWarning.Text = "数据上传成功";
                Response.Write("<script>alert('数据上传成功！');</script>");
                return filename;
            }
            else
            {
                Response.Write("<script language='Javascript'>alert('请选择txt xlsx tsv文件！');history.go(-1);</script>");
                return null;
            }
        }

        public void fillGrid(string filename, string excelName)
        {
            string conn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + Server.MapPath("fileupdown\\") + filename + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'"; //此连接可以操作.xls与.xlsx文件  
            string sqlin = "SELECT * FROM [" + excelName + "$]";
            OleDbCommand olecommand = new OleDbCommand(sqlin, new OleDbConnection(conn));
            OleDbDataAdapter adapterin = new OleDbDataAdapter(olecommand);
            //DataSet dsin = new DataSet();
            adapterin.Fill(ds);
            this.GridView1.DataSource = ds.Tables[0];
            this.GridView1.DataBind();
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            fillGrid(hidFileName.Value, "Data");

            SqlConnection conn = null;
            DB.Connection(ref conn);
            SqlTransaction trans = conn.BeginTransaction();
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = conn;
            sqlCommand.Transaction = trans;

            try
            {
                ds.Tables[0].Rows[2].Delete();
                ds.Tables[0].Rows[1].Delete();
                ds.Tables[0].Rows[0].Delete();
                ds.AcceptChanges();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {

                    //添加客户信息
                    sqlCommand.Parameters.Clear();
                    string cparty_name = dr[1].ToString();
                    string phone = dr[18].ToString();
                    string id_type = dr[19].ToString();
                    string id_no = dr[20].ToString();
                    string sex = dr[21].ToString();
                    string birthday = dr[22].ToString();
                    string email = dr[23].ToString();
                    string zipcode = dr[24].ToString();
                    string address = dr[25].ToString();
                    string cpy_type = "customer";
                    string entity = dr[12].ToString();
                    string cparty_no = "";
                    sqlCommand.Parameters.Add("@cparty_name", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@phone", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@id_type", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@id_no", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@sex", SqlDbType.NVarChar, 250);
                    sqlCommand.Parameters.Add("@birthday", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@email", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@zipcode", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@address", SqlDbType.NVarChar, 250);
                    sqlCommand.Parameters.Add("@cpy_type", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@entity", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@cparty_no", SqlDbType.VarChar, 250);

                    sqlCommand.Parameters["@cparty_name"].Value = cparty_name;
                    sqlCommand.Parameters["@phone"].Value = phone;
                    sqlCommand.Parameters["@id_type"].Value = id_type;
                    sqlCommand.Parameters["@id_no"].Value = id_no;
                    sqlCommand.Parameters["@sex"].Value = sex;
                    sqlCommand.Parameters["@birthday"].Value = birthday;
                    sqlCommand.Parameters["@email"].Value = email;
                    sqlCommand.Parameters["@zipcode"].Value = zipcode;
                    sqlCommand.Parameters["@address"].Value = address;
                    sqlCommand.Parameters["@cpy_type"].Value = cpy_type;
                    sqlCommand.Parameters["@entity"].Value = entity;
                    sqlCommand.Parameters["@cparty_no"].Direction = ParameterDirection.Output;


                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandText = "eCard_cparty_ImportInsert";
                    sqlCommand.ExecuteNonQuery();

                    cparty_no = sqlCommand.Parameters["@cparty_no"].Value.ToString();
                    //添加mmdeals

                    sqlCommand.Parameters.Clear();
                    string prod_code = dr[2].ToString();
                    string period = dr[3].ToString();
                    string card_no = dr[4].ToString();
                    string real_count = dr[5].ToString();
                    string real_discount = dr[6].ToString();
                    string incard_count = dr[7].ToString();
                    string value_date = dr[16].ToString();
                    string due_date = dr[17].ToString();
                    string incard_date = dr[15].ToString();
                    //string interest = dr[8].ToString();
                    string dealer = dr[26].ToString();
                    string remark = dr[27].ToString();
                    string ifneed_again = dr[28].ToString();
                    string again_period = dr[29].ToString();

                    string ifis_again = "N";
                    if (dr[30].ToString().Trim().Equals("Y"))
                        ifis_again = "Y";   //dr[30].ToString();      //是否是续存
                    string owner_cardno = dr[31].ToString();          //原始卡号

                    string if_sale_agent = dr[33].ToString();       //是否代理
                    string sale_agent_name = dr[34].ToString();     //代理名称
                    string agent_price_spread = dr[35].ToString();  //代理差价

                    string if_refund = "N";                         //是否退款
                    if (dr[36].ToString().Trim().Equals("Y"))
                        if_refund = "Y";
                    string refund_date =dr[37].ToString().Trim();   //退款日期                     


                    string if_reward = "N";                         //是否已经奖励
                    if (dr[38].ToString().Trim().Equals("Y"))
                        if_reward = "Y";
                    string reward_input_date=dr[39].ToString();     
                    string reward_cost_month =dr[40].ToString();

                    string if_settle = "N";                         //是否已经结算
                    if (dr[41].ToString().Trim().Equals("Y"))
                        if_settle = "Y";
                    string settle_agent_name =dr[42].ToString();
                    string settle_input_date = dr[43].ToString();
                    string settle_date = dr[44].ToString();

                    string settle_amount = dr[45].ToString();    //结算金额
                    string settle_eBank_Fee = dr[46].ToString(); //网银费用
                    string settle_charge = dr[47].ToString();    //结算手续费
                    string settle_remark = dr[48].ToString();    //结算备注

                    string deal_no = "";

                    //sqlCommand.Parameters.Add("@cparty_name", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@cparty_id", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@prod_code", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@period", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@card_no", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@real_count", SqlDbType.Decimal);
                    sqlCommand.Parameters.Add("@real_discount", SqlDbType.Decimal);
                    sqlCommand.Parameters.Add("@incard_count", SqlDbType.Decimal);
                    sqlCommand.Parameters.Add("@value_date", SqlDbType.DateTime);
                    sqlCommand.Parameters.Add("@due_date", SqlDbType.DateTime);
                    sqlCommand.Parameters.Add("@incard_date", SqlDbType.VarChar,250);
                    sqlCommand.Parameters.Add("@entity", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@dealer", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@remark", SqlDbType.NVarChar, 250);
                    sqlCommand.Parameters.Add("@ifneed_again", SqlDbType.VarChar,50);
                    sqlCommand.Parameters.Add("@again_period", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@ifis_again", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@owner_cardno", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@if_sale_agent", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@sale_agent_name", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@agent_price_spread", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@if_refund", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@refund_date", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@if_reward", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@reward_input_date", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@reward_cost_month", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@if_settle", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@settle_agent_name", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@settle_input_date", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@settle_date", SqlDbType.VarChar, 50);

                    sqlCommand.Parameters.Add("@settle_amount", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@settle_eBank_Fee", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@settle_charge", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@settle_remark", SqlDbType.VarChar, 500);


                    sqlCommand.Parameters.Add("@deal_no", SqlDbType.NVarChar, 250);


                    sqlCommand.Parameters["@cparty_id"].Value = cparty_no;
                    sqlCommand.Parameters["@prod_code"].Value = prod_code;
                    sqlCommand.Parameters["@period"].Value = period;
                    sqlCommand.Parameters["@card_no"].Value = card_no;
                    sqlCommand.Parameters["@real_count"].Value = real_count;
                    sqlCommand.Parameters["@real_discount"].Value = real_discount;
                    sqlCommand.Parameters["@incard_count"].Value = incard_count;
                    sqlCommand.Parameters["@value_date"].Value = value_date;
                    sqlCommand.Parameters["@due_date"].Value = due_date;
                    sqlCommand.Parameters["@incard_date"].Value = incard_date;
                    sqlCommand.Parameters["@entity"].Value = entity;
                    sqlCommand.Parameters["@dealer"].Value = dealer;
                    sqlCommand.Parameters["@remark"].Value = remark;
                    sqlCommand.Parameters["@ifneed_again"].Value = ifneed_again;
                    sqlCommand.Parameters["@again_period"].Value = again_period;

                    sqlCommand.Parameters["@ifis_again"].Value = ifis_again;
                    sqlCommand.Parameters["@owner_cardno"].Value = owner_cardno;
                    sqlCommand.Parameters["@if_sale_agent"].Value = if_sale_agent;
                    sqlCommand.Parameters["@sale_agent_name"].Value = sale_agent_name;
                    sqlCommand.Parameters["@agent_price_spread"].Value = agent_price_spread;

                    sqlCommand.Parameters["@if_refund"].Value = if_refund;
                    sqlCommand.Parameters["@refund_date"].Value = refund_date;
                    sqlCommand.Parameters["@if_reward"].Value = if_reward;
                    sqlCommand.Parameters["@reward_input_date"].Value = reward_input_date;
                    sqlCommand.Parameters["@reward_cost_month"].Value = reward_cost_month;
                    sqlCommand.Parameters["@if_settle"].Value = if_settle;
                    sqlCommand.Parameters["@settle_agent_name"].Value = settle_agent_name;
                    sqlCommand.Parameters["@settle_input_date"].Value = settle_input_date;
                    sqlCommand.Parameters["@settle_date"].Value = settle_date;

                    sqlCommand.Parameters["@settle_amount"].Value = settle_amount;
                    sqlCommand.Parameters["@settle_eBank_Fee"].Value = settle_eBank_Fee;
                    sqlCommand.Parameters["@settle_charge"].Value = settle_charge;
                    sqlCommand.Parameters["@settle_remark"].Value = settle_remark;

                    sqlCommand.Parameters["@deal_no"].Direction = ParameterDirection.Output;

                    //KVB.Common.Log.WriteDefaultLog(cparty_name+":", id_type.ToString()+":" +id_no.ToString());

                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandText = "eCard_mmdeal_ImportInsert";
                    sqlCommand.ExecuteNonQuery();

                    deal_no = sqlCommand.Parameters["@deal_no"].Value.ToString();

                    //添加Remittance
                    sqlCommand.Parameters.Clear();
                    string account_name = dr[8].ToString();
                    string bank_name = dr[9].ToString();
                    string account_no = dr[10].ToString();
                    string transfer_type = dr[11].ToString();
                    string transfer_amount = dr[5].ToString();
                    string to_account = dr[13].ToString();
                    string remit_date =dr[14].ToString();

                    //续存的单子是没有汇款信息的。只有原始单子才有汇款信息
                    //退款的单子叶不需要汇款信息(还是放进去吧，因为真实的账根本无法管理)
                    if (ifis_again.Equals("N")) // ||  if_refund.Equals("N")        
                    {
                        sqlCommand.Parameters.Add("@card_no", SqlDbType.VarChar, 50);
                        sqlCommand.Parameters.Add("@account_name", SqlDbType.NVarChar, 50);
                        sqlCommand.Parameters.Add("@bank_name", SqlDbType.NVarChar, 100);
                        sqlCommand.Parameters.Add("@account_no", SqlDbType.VarChar, 50);
                        sqlCommand.Parameters.Add("@transfer_type", SqlDbType.VarChar, 50);
                        sqlCommand.Parameters.Add("@transfer_amount", SqlDbType.Decimal);
                        sqlCommand.Parameters.Add("@to_account", SqlDbType.NVarChar, 50);
                        sqlCommand.Parameters.Add("@remit_date", SqlDbType.VarChar, 50);
                        sqlCommand.Parameters.Add("@remark", SqlDbType.NVarChar, 250);

                        sqlCommand.Parameters["@card_no"].Value = card_no;
                        sqlCommand.Parameters["@account_name"].Value = account_name;
                        sqlCommand.Parameters["@bank_name"].Value = bank_name;
                        sqlCommand.Parameters["@account_no"].Value = account_no;
                        sqlCommand.Parameters["@transfer_type"].Value = transfer_type;
                        sqlCommand.Parameters["@transfer_amount"].Value = transfer_amount;
                        sqlCommand.Parameters["@to_account"].Value = to_account;
                        sqlCommand.Parameters["@remit_date"].Value = remit_date;
                        sqlCommand.Parameters["@remark"].Value = remark;

                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlCommand.CommandText = "eCard_transfer_ImportInsert";
                        sqlCommand.ExecuteNonQuery();
                    }

                    //如果已经结算完则不用添加cflow数据,退款也不需要cflow了。
                    if (if_settle.Equals("N") && if_refund.Equals("N"))
                    {
                        //添加cflows
                        string sale_amount = dr[32].ToString();

                        sqlCommand.Parameters.Clear();
                        sqlCommand.Parameters.Add("@card_no", SqlDbType.VarChar, 50);
                        sqlCommand.Parameters.Add("@cflow_date", SqlDbType.DateTime);
                        sqlCommand.Parameters.Add("@amount", SqlDbType.Decimal);
                        sqlCommand.Parameters.Add("@sale_amount", SqlDbType.VarChar, 20);
                        sqlCommand.Parameters.Add("@deal_no", SqlDbType.VarChar, 50);

                        sqlCommand.Parameters["@card_no"].Value = card_no;
                        sqlCommand.Parameters["@cflow_date"].Value = value_date;
                        sqlCommand.Parameters["@amount"].Value = incard_count;
                        sqlCommand.Parameters["@sale_amount"].Value = sale_amount;
                        sqlCommand.Parameters["@deal_no"].Value = deal_no;

                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlCommand.CommandText = "eCard_cflow_ImportInsert";
                        sqlCommand.ExecuteNonQuery();
                    }
                }
                trans.Commit();
                Response.Write("<script>alert('导入数据成功！');</script>");
                lblWarning.Text = "导入数据成功";
            }
            catch (Exception ex)
            {
                lblWarning.Text = "导入数据出错";
                KVB.Common.Log.WriteDefaultLog("CpartyDao.AddCparty", ex.ToString());
                trans.Rollback();
                Response.Write("<script>alert('导入数据出错！'+"+ex.ToString()+");</script>");
            }
            finally
            {
                DB.ConnDispose(ref conn);
                trans.Dispose();
                conn.Dispose();
            }
            #region old
            //sql(filename, "Data", "");
            /*
            DataTable dtin = dsin.Tables[0];//连接并读取excel数据
            //if (!existedName(dsin.Tables[0].Rows[1].ToString()))
            //{
            for (int i = 0; i < dsin.Tables[0].Rows.Count; i++)//将Excel文件中第一个工作表的数据导入到sql数据库scjd_youliaoxiaohaojihua表中
            {
                string sql = "insert into " + tablename + " values('" + dsin.Tables[0].Rows[i]["订单流水"] + "','" + dsin.Tables[0].Rows[i]["用户号码"] + "','" + dsin.Tables[0].Rows[i]["用户品牌"] + "','" + dsin.Tables[0].Rows[i]["用户县市"] + "','" + dsin.Tables[0].Rows[i]["订单主题"] + "' ,'" + dsin.Tables[0].Rows[i]["订单类型"] + "','" + dsin.Tables[0].Rows[i]["订单内容"] + "','" + dsin.Tables[0].Rows[i]["紧急程度"] + "','" + dsin.Tables[0].Rows[i]["订单状态"] + "','" + dsin.Tables[0].Rows[i]["受理时间"] + "','" + dsin.Tables[0].Rows[i]["要求处理时限"] + "' ,'" + dsin.Tables[0].Rows[i]["受理部门"] + "','" + dsin.Tables[0].Rows[i]["受理人"] + "','" + dsin.Tables[0].Rows[i]["处理过程"] + "','" + dsin.Tables[0].Rows[i]["是否当场归档"] + "','" + dsin.Tables[0].Rows[i]["归档时间"] + "','" + dsin.Tables[0].Rows[i]["归档部门"] + "','" + dsin.Tables[0].Rows[i]["归档人"] + "','" + dsin.Tables[0].Rows[i]["责任原因"] + "','" + dsin.Tables[0].Rows[i]["责任方"] + "','" + dsin.Tables[0].Rows[i]["满意度"] + "','" + dsin.Tables[0].Rows[i]["解决程度"] + "')";
                try
                {
                    page.DoSql(sql);
                }
                catch (Exception ex)
                {
                    Response.Write("<script>alert('数据导入错误，请检查Excel文件')</script>");
                    return;
                }
                //}
            }
            FileInfo file = new FileInfo(Server.MapPath("fileupdown/") + filename);
            if (file.Exists)
            {//删除文件
                file.Delete();
            }
             * */
            #endregion
        }
    }
}